Create PROC [dbo].[SpCalcSoodSaham]
    @SoodSal FLOAT,
    @PrKharid FLOAT,
    @PrSarmaye FLOAT,
    @PurchaseCeilling MONEY,
    @StartDate SMALLDATETIME,
    @EndDate SMALLDATETIME,
    @Msg INT OUTPUT
AS
DECLARE @Tcode AS NVARCHAR(20);
DECLARE @SumKharid MONEY;
DECLARE @Sarmaye MONEY;
DECLARE @Counter INT;

DECLARE @tblSahamForosh TABLE
(
    Guy_Code VARCHAR(5),
    Name NVARCHAR(90),
    Saham_Moaser MONEY,
    Saham MONEY,
    Kharid_Khales MONEY
);

DECLARE @tblKharidKhales TABLE
(
    Guy_Code VARCHAR(5),
    Kharid_Khales MONEY,
    Tot_Price_forosh MONEY,
    Tot_Price_Bargasht MONEY
);
--------------------------------------
SELECT @Tcode = Acc_Code
FROM classify
WHERE (classify.AutoId = 31);
------------------------------------------------
--محاسبه مبلغ سرفصل كل سرمايه   
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------*/

--محاسبه مبلغ كل خريد اعضا
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------*/

INSERT INTO @tblSahamForosh
(
    Guy_Code,
    Name,
    Saham_Moaser,
    Saham,
    Kharid_Khales
)
SELECT SUBSTRING(tblSaham.Acc_Code, LEN(@Tcode) + 1, LEN(tblSaham.Acc_Code)) AS Guy_Code,
       tblSaham.Acc_Name,
       SUM(tblSaham.Saham_Moaser) AS Saham_Moaser,
       SUM(tblSaham.Saham) AS Saham,
       0
FROM
(
    SELECT tInKol.Acc_Code,
           SUM(tInKol.Saham_Moaser) AS Saham_Moaser,
           SUM(Saham) Saham,
           Acc_Name
    FROM
    (
        SELECT tblBedBes.Acc_Code,
       SUM(tblBedBes.Saham_Moaser) Saham_Moaser,
       SUM(tblBedBes.Saham) Saham
FROM
(
    SELECT Acc_Code,
           Saham_Moaser =  ((360
                        - (((CAST(SUBSTRING(LTRIM((dbo.MiladiToShamsi(HeadS.Tarikh_User))), 6, 2) AS MONEY)
                             - 1
                            ) * 30
                           )
                           + CAST(SUBSTRING(LTRIM((dbo.MiladiToShamsi(HeadS.Tarikh_User))), 9, 2) AS MONEY)
                          )
                       )
                       / 360
                          )
                          * SUM(   CASE
                                       WHEN Sanad.BESTAN > 0 THEN
                                           Sanad.BESTAN
                                       ELSE
                                           -1 * Sanad.BEDEH
                                   END
                               ),
           Saham = SUM(   CASE
                              WHEN dbo.Sanad.BESTAN > 0 THEN
                                  Sanad.BESTAN
                              WHEN Sanad.BEDEH > 0 THEN
                                  -1 * Sanad.BEDEH
                          END
                      )
    FROM Sanad
        INNER JOIN HeadS
            ON Sanad.Id_Sanad = HeadS.Id_Sanad
    WHERE (Acc_Code LIKE @TCode + '%')
          AND (HeadS.LockKing <> '9')
    GROUP BY Acc_Code,
             HeadS.Tarikh_User
) AS tblBedBes
GROUP BY tblBedBes.Acc_Code

    ) AS tInKol
        INNER JOIN dbo.Account
            ON Account.Acc_Code = tInKol.Acc_Code
    GROUP BY tInKol.Acc_Code,
             dbo.Account.Acc_Name
    HAVING SUM(tInKol.Saham_Moaser) > 0
) tblSaham
GROUP BY tblSaham.Acc_Code,
         tblSaham.Acc_Name;
------------------------------------------
INSERT INTO @tblKharidKhales
(
    Guy_Code,
    Kharid_Khales,
    Tot_Price_forosh,
    Tot_Price_Bargasht
)
SELECT SalesRet.Guy_Code,
       SUM(SalesRet.Tot_Price_Forosh - SalesRet.Tot_Price_Bargasht),
       SUM(Tot_Price_Forosh) Tot_Price_forosh,
       SUM(Tot_Price_Bargasht) Tot_Price_Bargasht
FROM
(
    SELECT Tot_Price_Forosh = ISNULL(   CASE
                                            WHEN Kind_Flag IN ( '7', '5', '9' ) THEN
                                        (dbo.headH.Tot_Price_forosh)
                                        END,
                                        0
                                    ),
           Tot_Price_Bargasht = ISNULL(   CASE
                                              WHEN Kind_Flag IN ( '0', '6', '8' ) THEN
                                          (dbo.headH.Tot_Price_KHarid)
                                          END,
                                          0
                                      ),
           Guy_Code = CASE
                          WHEN Kind_Flag IN ( '7', '5', '9' ) THEN
                              Reciver
                          WHEN Kind_Flag IN ( '0', '6', '8' ) THEN
                              Sender
                      END
    FROM headH
        INNER JOIN Havaleh
            ON Havaleh.Id_Havaleh = headH.Id_Havaleh
    WHERE headH.Tarikh_faktor
          BETWEEN @StartDate AND @EndDate
          AND dbo.Havaleh.Sender <> '10000'
          AND dbo.Havaleh.Reciver <> '10000'
          AND Kind_Flag IN ( '7', '5', '9', '0', '6', '8' )
    GROUP BY Havaleh.Id_Havaleh,
             CASE
                 WHEN Kind_Flag IN ( '7', '5', '9' ) THEN
                     Reciver
                 WHEN Kind_Flag IN ( '0', '6', '8' ) THEN
                     Sender
             END,
             Kind_Flag,
             Tot_Price_Forosh,
             Tot_Price_KHarid
) AS SalesRet
GROUP BY SalesRet.Guy_Code;
------------------------------------------
UPDATE @tblSahamForosh
SET Kharid_Khales = CASE
                        WHEN @PurchaseCeilling > 0
                             AND [@tblKharidKhales].Kharid_Khales > @PurchaseCeilling THEN
                            @PurchaseCeilling
                        ELSE
                            [@tblKharidKhales].Kharid_Khales
                    END
FROM @tblSahamForosh
    INNER JOIN @tblKharidKhales
        ON [@tblKharidKhales].Guy_Code = [@tblSahamForosh].Guy_Code;
------------------------------------------------
SELECT @SumKharid = SUM(Kharid_Khales),
       @Counter = COUNT(Kharid_Khales),
       @Sarmaye = SUM(Saham_Moaser)
FROM @tblSahamForosh;
--------------

--SELECT * FROM @tblSahamForosh
--INNER JOIN @tblKharidKhales ON [@tblKharidKhales].Guy_Code = [@tblSahamForosh].Guy_Code
-----------------------------------------------------------------------------------------------------
--محاسبه مبلغ سود هر عضو
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------*/
IF @SumKharid <= 0
    SET @SumKharid = 1;
IF (@Sarmaye > 0) --and   (@Counter > 0)  and (@SumKharid > 0)
BEGIN
    SELECT Acc_Code,
           Guy_Code,
           Name,
           Khales_Forosh,
           ((@PrKharid * Sood_Kharid) / 100) AS Sood_kharid,
           Saham,
           ((@PrSarmaye * Sood_Sarmaye) / 100) AS Sood_Sarmaye,
           (((@PrKharid * Sood_Kharid) / 100) + ((@PrSarmaye * Sood_Sarmaye) / 100)) AS FinalSood,
           Saham1
    FROM
    (
        SELECT Acc_Code,
               Guy_Code,
               Name,
               Kharid_Khales Khales_Forosh,
               (@SoodSal * Kharid_Khales) / @SumKharid AS Sood_Kharid,
               Saham,
               ((@SoodSal * Saham) / @Sarmaye) AS Sood_Sarmaye,
               Saham1
        FROM
        (
            SELECT @Tcode + [@tblSahamForosh].Guy_Code Acc_Code,
                   [@tblSahamForosh].Guy_Code,
                   [@tblSahamForosh].Name,
                   Tot_Price_forosh,
                   Tot_Price_Bargasht,
                   [@tblSahamForosh].Kharid_Khales,
                   Saham_Moaser Saham,
                   Saham Saham1
            FROM @tblSahamForosh
                LEFT OUTER JOIN @tblKharidKhales
                    ON [@tblKharidKhales].Guy_Code = [@tblSahamForosh].Guy_Code
        ) Table3
    ) TbMain;

    ---------------------------------------------------------------
    SET @Msg = 1;
END;
ELSE IF (@Sarmaye <= 0)
    SET @Msg = -1; --sarmaye is negative
ELSE IF (@SumKharid < 0) -- (@SumKharid <= 0)
    SET @Msg = -2; --sumkharid is negative
ELSE IF (@Counter = 0)
    SET @Msg = -3;
